Hive SQL

作者 chris.Yun 日期 2018-08-14
Hive
Hive SQL

文章中部分内容转至该链接：https://blog.csdn.net/qq_806913882/article/details/53576356

Hive是建立在Hadoop HDFS基础之上的数据仓库基础框架。

  • Hive的数据是保存在HDFS上，Hive的表其实就是HDFS的目录，Hive的数据其实就是HDFS的文件
  • Hive定义了简单的类似SQL查询语言，称为HQL
  • Hive是SQL解析引擎，它将SQL语句转移成M/R Job，然后在Hadoop上执行
  • Hive支持开发者自定义M/R Job

字段

简单类型

  • 整数 tinyint(1字节)/smallint(2字节)/int(4字节)/bigint(8字节)
  • 浮点数 float(4字节)/double(8字节)
  • 布尔类型 boolean
  • 字符串 string(最大2GB)

复杂类型

  • Array
  • Struct
  • Map

表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
  [(col_name data_type [COMMENT col_comment], ...)] 
  [COMMENT table_comment] 
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
  [CLUSTERED BY (col_name, col_name, ...) 
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
  [ROW FORMAT row_format] 
  [STORED AS file_format] 
  [LOCATION hdfs_path]

# 字段解释：
[external] # 申明是否为外部表

[if not exists] # 如果表存在，则不创建了

[(col_name data_type [COMMENT col_comment], ...)] # 分别是 字段名，字段类型，字段注释

[COMMENT table_comment] # 放在()后面，是表注释

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] # 建立分区，()里是字段名，字段类型，字段注释，分区里的字段不能包含在字段声明区。

[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] # 在clustered by 里的字段上建立 num_buckets个桶，记录是由 sorted by里的字段排序的。

[ROW FORMAT row_format] # 指定分隔符，可以是以下几个：
        : DELIMITED [FIELDS TERMINATED BY char] 
                    [COLLECTION ITEMS TERMINATED BY char] 
                    [MAP KEYS TERMINATED BY char] 

[STORED AS file_format] # 指定load文件的类型，分为：
            textfile 是 纯文本文件
            sequence 是 压缩文件

内部表（Table）

创建一个简单内部表，列之间通过,分割，行与行之间通过换行符分割，纯文本方式存储。内部表删除时，元数据及表文件都会删除。

create table if not exists t3 (
    tid int, 
    tname string, 
    age int;
) COMMENT 'table comment'
row format delimited 
    fields terminated by ','
    lines terminated by '\n';
stored as textfile;

复制一个空表
CREATE TABLE empty_key_value_store LIKE key_value_store;

分区表（Partition Table）

创建分区表，指定分区字段，分区字段与表的字段不能重复。表中的一个Partition对应于表下的一个目录，所有的Partition的数据都存储在对应的目录中。

create table if not exists T3(
    id int,
    name string
)
partitioned by (classid int)
row format delimited fields terminated by ','
stored as textfile;

桶表（Bucket Table）

创建桶表，根据age建立5个桶，桶里的字段根据name排序，桶表是对数据进行哈希取值，然后放到不同文件中存储。
在相同列上划分了桶的表，JOIN操作时会比较高效。同时，取样（sampling）更高效。

create table T4(
    id int ,
    name string,
    sex string,
    age int
)
partitioned by (city string)
clustered by(age) sorted by(name) into 5 buckets
row format delimited fields terminated by ','
stored as textfile;

外部表（External Table）

指向已经在HDFS中存在的数据的表，它也可以来创建Partiton，元数据与内部表一致，实际的数据存储有比较大的差异。删除外部表时，只删除了元数据。

create external table if not exists T1(
    id int,
    name string,
    salary double comment 'comment是字段注释'
)comment 'comment是表注释'
row format 
delimited  fields terminated by ','
stored as textfile
location '/input';

常用命令

load

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]  

#字段解释
[LOCAL] # 加载的是否是本地文件
[OVERWRITE] # 是否覆盖表里原来的数据   
[PARTITION (partcol1=val1, partcol2=val2 ...)]   # 如果表存在分区，指定加载到那个分区

example

#加载本地文件
LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1
#加载HDFS上的文件
LOAD DATA INPATH '/user/admin/test/test.txt' OVERWRITE INTO TABLE test_1

insert

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement  

#字段解释
[PARTITION (partcol1=val1, partcol2=val2 ...)] # 如果表存在分区，指定加载到那个分区

example

insert overwrite table t5 select * from t2;   
insert into table t5 select * from t2;

select

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[   CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

# 字段解释
[ALL | DISTINCT] # 查询的字段

[WHERE where_condition] # where子句
                     # 类似我们传统SQL的where 条件
                     # 目前支持 AND,OR ,0.9版本支持between
                     # IN, NOT IN
                     # 不支持EXIST ,NOT EXIST

[GROUP BY col_list [HAVING condition]] # 字段分组和having子句

[SORT BY| ORDER BY col_list]  # 排序
                        # ORDER BY与SORT BY的不同
                        # ORDER BY 全局排序，只有一个Reduce任务
                        # SORT BY 只在本机做排序

[LIMIT number] # 限制查询记录数

join

hive 多表连接使用 join…on 语句
hive 只支持等值连接，即on子句中只能使用等号连接
如果连接语句中有where子句，会先执行join子句，再执行where子句。

函数

函数比较多，可以参考别人的：HIVE函数参考手册

显示命令

show tables;
show databases;
show partitions;
show functions;
describe extended table_name dot col_name;

Hive优化

  • 尽早尽量过滤数据，减少每个阶段的数据量
  1. 列裁剪。只读取相关的列，避免全表扫描。
  2. 分区表指定分区查询。
  3. mulit-insert。同一张表多维度查询插入只需要扫描一次。
    from a
    insert overwrite table t1 select col1,col2,... where a.key='key1'
insert overwrite table t2 select col5,col6,... where a.key='key2'
  4. 数据越早过滤越好，子查询中优先过滤。
  • 减少job数
  1. 利用mapjoin(b),b为小表，小表与大表关联时，一次性把小表加载到内存中。select /+mapjoin(b)+/ a.key,a.value from a left join b on a.key=b.key
  2. join优化。多表关联时，如果join的key相同，只会有一个job。
  3. union all优化。
    select from (select c1,c2 from A group by c1,c2 union all select c1,c2 from B group by c1,c2) – 2个job
    select     from (select c1,c2 from A union all select c1,c2 from B) group by c1,c2 – 1个job
  • 解决数据倾斜问题
  1. 空值产生。
  2. 不同的数据类型关联产生。
  3. 关联的key值非空，但是某个值大量重复。
  4. distinct, count(distinct)，通过group by 去重解决。
FEATURED TAGS
Hive
FRIENDS